clear all


set haverdir "C:\Haver", perm
import haver C??@HWOL, clear

drop ca0_hwol- ca9_hwol cb0_hwol-cc9_hwol cd0_hwol-cdc_hwol cus_hwol

gen year=year(dofm(time))
collapse (mean) c*, by(year)
reshape long c, i(year) j(state) string

replace state=upper(substr(state,1,2))
rename state st
rename c HWOLsupdemrate
label variable HWOLsupdemrate "supply/demand rate from HWOL-- annual average of monthly (SA) estimates"

merge m:1 st using ./reference/statefips.dta, keep(match) nogen
sort statefip year
save hwol_annual_supply_demand_rate_bystate_sa.dta, replace


set haverdir "C:\Haver"
import haver NC??@HWOL, clear
drop ncc1_hwol-ncc9_hwol ncus_hwol ncdc_hwol

gen year=year(dofm(time))
collapse (mean) nc*, by(year)
reshape long nc, i(year) j(state) string

replace state=upper(substr(state,1,2))
rename state st
rename nc HWOLsupdemrate
label variable HWOLsupdemrate "supply/demand rate from HWOL-- annual average of monthly (NSA) estimates"

merge m:1 st using ./reference/statefips.dta, keep(match) nogen
sort statefip year
save hwol_annual_supply_demand_rate_bystate_nsa.dta, replace


set haverdir "C:/Haver/"
import haver NED???@HWOL, clear
*dc drop
drop  ned?dc_hwol

gen year=year(dofm(time))
drop if year==2005 //don't have full 2005 time series
collapse (sum) ned*, by(year)
reshape long ned, i(year) j(state) string
replace ned=ned/12 //total annual postings divided by 12
gen occsoc_broadgroup=substr(state,1,1)
destring occsoc_broadgroup, replace
replace state=upper(substr(state,2,2))
rename state st

label define occsoc_broadgroup 2 "SOC 11-13" 3 "SOC 15-29" 4 "SOC 31-39" 5 "SOC 41-43" 6 "SOC 45-49" 7 "SOC 51-53"
label values occsoc_broadgroup occsoc_broadgroup

merge m:1 st using "S:/reference/statefips.dta", keep(match) nogen

save hwol_total_postings_broad_occgroup.dta, replace

preserve
merge 1:1 statefip occsoc_broadgroup year using ACS_unemplevel_state_occsoc_broadgroup.dta, keep(master match) nogen
gen HWOLsupdemrate_occsoc_broad=(count/ned)
keep if HWOLsupdemrate_occsoc_broad!=.
keep year statefip HWOLsupdemrate_occsoc_broad occsoc_broadgroup

save hwol_annual_supply_demand_rate_bystate_occsoc_broadgroup_nsa.dta, replace
restore

merge 1:1 statefip occsoc_broadgroup year using cps_unicon_allcovars.dta, keep(master match) nogen
gen HWOLsupdemrate_occsoc_broad_cps=(unemp_occsoc_broadgroup/ned)
keep if HWOLsupdemrate_occsoc_broad_cps!=.
keep year statefip HWOLsupdemrate_occsoc_broad_cps occsoc_broadgroup

save hwol_annual_supply_demand_rate_bystate_occsoc_broadgroup_nsa_cps.dta, replace
